{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>name</th>\n",
       "      <th>wholesale_price</th>\n",
       "      <th>retail_price</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>computer</td>\n",
       "      <td>500.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96</td>\n",
       "      <td>Python Workout</td>\n",
       "      <td>35.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97</td>\n",
       "      <td>Pandas Workout</td>\n",
       "      <td>35.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>banana</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87</td>\n",
       "      <td>sandwich</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24</td>\n",
       "      <td>phone</td>\n",
       "      <td>200.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>apple</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17</td>\n",
       "      <td>pear</td>\n",
       "      <td>0.6</td>\n",
       "      <td>1.2</td>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id            name  wholesale_price  retail_price   sales\n",
       "0          23        computer            500.0        1000.0   100.0\n",
       "1          96  Python Workout             35.0          75.0  1000.0\n",
       "2          97  Pandas Workout             35.0          75.0   500.0\n",
       "3          15          banana              0.5           1.0   200.0\n",
       "4          87        sandwich              3.0           5.0   300.0\n",
       "5          24           phone            200.0         500.0   100.0\n",
       "6          16           apple              0.5           1.0   200.0\n",
       "7          17            pear              0.6           1.2    75.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "\n",
    "df = DataFrame([{'product_id':23, 'name':'computer', 'wholesale_price': 500,\n",
    "                 'retail_price':1000, 'sales':100},\n",
    "               {'product_id':96, 'name':'Python Workout', 'wholesale_price': 35,\n",
    "                'retail_price':75, 'sales':1000},\n",
    "               {'product_id':97, 'name':'Pandas Workout', 'wholesale_price': 35,\n",
    "                'retail_price':75, 'sales':500},\n",
    "               {'product_id':15, 'name':'banana', 'wholesale_price': 0.5,\n",
    "                'retail_price':1, 'sales':200},\n",
    "               {'product_id':87, 'name':'sandwich', 'wholesale_price': 3,\n",
    "                'retail_price':5, 'sales':300},\n",
    "               ])\n",
    "\n",
    "\n",
    "new_products = DataFrame([{'product_id':24, 'name':'phone', 'wholesale_price': 200,\n",
    "                 'retail_price':500},\n",
    "                        {'product_id':16, 'name':'apple', 'wholesale_price': 0.5,\n",
    "                 'retail_price':1},\n",
    "                        {'product_id':17, 'name':'pear', 'wholesale_price': 0.6,\n",
    "                 'retail_price':1.2}], index=range(5,8))\n",
    "\n",
    "df = pd.concat([df, new_products])\n",
    "df.loc[[5,6,7], 'sales'] = [100, 200, 75]\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "\n",
    "Add one new product to the data frame, without using `pd.concat`. What's the advantage of `pd.concat`, and when should you use it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you're just adding one row to the data frame, you can assign \n",
    "# to df.loc[INDEX].  Assuming that the index doesn't already exist,\n",
    "# this will add a new row to the data frame.  You can, of course,\n",
    "# also use df.iloc[INDEX], using a number 1 greater than the current\n",
    "# max.\n",
    "\n",
    "# By contrast, pd.concat is for when you want to combine to \n",
    "# data frames into a single new one\n",
    "df.loc[8] = [99, 'persimmon', 2, 4.5, 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "Add a new column, `department`, to the data frame. Place each product in a separate department. For example, in our data, we would have three departments: `electronics`, `books`, and `food`. Calculate `current_net` on the data frame, and then show the descriptive statisics for `current_net` for food products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>name</th>\n",
       "      <th>wholesale_price</th>\n",
       "      <th>retail_price</th>\n",
       "      <th>sales</th>\n",
       "      <th>department</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23</td>\n",
       "      <td>computer</td>\n",
       "      <td>500.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>electronics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>96</td>\n",
       "      <td>Python Workout</td>\n",
       "      <td>35.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97</td>\n",
       "      <td>Pandas Workout</td>\n",
       "      <td>35.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15</td>\n",
       "      <td>banana</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87</td>\n",
       "      <td>sandwich</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24</td>\n",
       "      <td>phone</td>\n",
       "      <td>200.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>electronics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>apple</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17</td>\n",
       "      <td>pear</td>\n",
       "      <td>0.6</td>\n",
       "      <td>1.2</td>\n",
       "      <td>75.0</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>99</td>\n",
       "      <td>persimmon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id            name  wholesale_price  retail_price   sales  \\\n",
       "0          23        computer            500.0        1000.0   100.0   \n",
       "1          96  Python Workout             35.0          75.0  1000.0   \n",
       "2          97  Pandas Workout             35.0          75.0   500.0   \n",
       "3          15          banana              0.5           1.0   200.0   \n",
       "4          87        sandwich              3.0           5.0   300.0   \n",
       "5          24           phone            200.0         500.0   100.0   \n",
       "6          16           apple              0.5           1.0   200.0   \n",
       "7          17            pear              0.6           1.2    75.0   \n",
       "8          99       persimmon              2.0           4.5     1.0   \n",
       "\n",
       "    department  \n",
       "0  electronics  \n",
       "1        books  \n",
       "2        books  \n",
       "3         food  \n",
       "4         food  \n",
       "5  electronics  \n",
       "6         food  \n",
       "7         food  \n",
       "8         food  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['department'] = ['electronics', 'books', 'books', 'food', 'food',\n",
    "                   'electronics', 'food', 'food', 'food']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2.000000e+00\n",
       "mean     9.904000e+05\n",
       "std      3.501593e+05\n",
       "min      7.428000e+05\n",
       "25%      8.666000e+05\n",
       "50%      9.904000e+05\n",
       "75%      1.114200e+06\n",
       "max      1.238000e+06\n",
       "Name: current_net, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['current_net'] = (df['retail_price'] - df['wholesale_price']) * df['sales'].sum()\n",
    "\n",
    "# Now use a mask index on current_net\n",
    "df['current_net'][df['department'] == 'electronics'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "Now use the `query` method to get the descriptive statistics for food items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       5.000000\n",
       "mean     3020.720000\n",
       "std      2371.020496\n",
       "min      1238.000000\n",
       "25%      1238.000000\n",
       "50%      1485.600000\n",
       "75%      4952.000000\n",
       "max      6190.000000\n",
       "Name: current_net, dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('department == \"food\"')['current_net'].describe()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
